<?php


namespace app\api\controller;

use app\common\model\SmokeRecord;
use think\Cache;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Index extends AuthBase
{
    public function index(){
        $userAgent = $_SERVER['HTTP_USER_AGENT'] ?? '';
        // 判断是否是iOS设备（iPhone/iPad）
        if (stripos($userAgent, 'iPhone') !== false || stripos($userAgent, 'iPad') !== false) {
            $device="苹果";
        }elseif(stripos($userAgent, 'Android') !== false){
            $device="安卓";
        }else{
            $device="小程序";
        }
        Db::name('user')
        ->where('id',$this->userId)
        ->update(['device_type'=>$device]);
        $user=Db::name('user')
            ->where('id',$this->userId)
            ->field('nickname,age_time')
            ->find();
        $user['age']=get_smoke_age($user['age_time']);
        $smoke=Db::name('smoke_record')
            ->where('user_id',$this->userId)
            ->whereTime('smoke_date','-30 day')
            ->sum('num');
        if ($smoke==0){
            $avg_smoke=0;
        }else{
            $avg_smoke=round($smoke/30,2);
        }
        $user['avg_smoke']=$avg_smoke;
        $user['level']=get_level($avg_smoke);
        $user['is_vip']=$this->is_vip;
        return $this->show(1,'success',$user);
    }
    public function statistics(){
        $type=input('get.type',1);
        $where=[];
        switch ($type){
            case 1:
                $one=strtotime('today');
                break;
            case 2:
                $one=strtotime(date('Y-m-01'));
                break;
            case 3:
                $currentYear = date('Y');
                $one = strtotime("{$currentYear}-01-01 00:00:00");
                break;
            case 4:
                $one=Db::name('smoke_record')
                    ->where('user_id',$this->userId)
                    ->order('smoke_date asc')
                    ->value('smoke_date')?:1;
                break;
        }
        $num=get_diff_day($one,time());
        $where[]=[
            'smoke_date','between',[$one,time()],
        ];
        $where[]=[
            'user_id','=',$this->userId
        ];
        $smoke=Db::name('smoke_record')
            ->where($where)
            ->sum('num');
        if ($smoke==0){
            $avg_smoke=0;
        }else{
            $avg_smoke=round($smoke/$num,2);
        }
        $data['avg_smoke']=$avg_smoke;
        $data['sum_smoke']=$smoke;
        //烟费
        $cost=Db::name('smoke_cost_record')
            ->where('user_id',$this->userId)
            ->whereTime('buy_date','between',[$one,time()])
            ->sum('price');
        if ($cost==0){
            $avg_cost=0;
        }else{
            $avg_cost=round($cost/$num,2);
        }
        $data['avg_cost']=$avg_cost;
        $data['cost']=$cost;
        return $this->show(1,'success',$data);
    }
    public function smoke_statistics(){
        $type=input('get.type',1);
        if (!$type){
            $type=1;
        }
        if ($type==1){
            //获取近6日日期
            $currentDate = date('Y-m-d');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m-d', strtotime("-$i days", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one);
                $result[$k]['num']=Db::name('smoke_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereDay('smoke_date',$v)
                    ->sum('num');
            }
        }elseif($type==2){
            $currentDate = date('Y-m');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m', strtotime("-$i month", strtotime($currentDate)));
                $dates[] = $date;
            }

            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one).'月';
                $result[$k]['num']=Db::name('smoke_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereMonth('smoke_date',$v)
                    ->sum('num');
            }
        }elseif($type==3){
            $currentDate = date('Y');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y', strtotime("-$i year", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $result[$k]['date']=$v;
                $result[$k]['num']=Db::name('smoke_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereYear('smoke_date',$v)
                    ->sum('num');
            }
        }
        $data['result']=array_reverse($result);;
        $data['is_vip']=$this->is_vip;
//        $data['y_max'] = max(array_column($result, 'num'));
        return show(1,'success',$data);
    }
    public function cost_statistics(){
        $type=input('get.type',1);
        if (!$type){
            $type=1;
        }
        if ($type==1){
            //获取近6日日期
            $currentDate = date('Y-m-d');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m-d', strtotime("-$i days", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one);
                $result[$k]['num']=Db::name('smoke_cost_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereDay('buy_date',$v)
                    ->sum('price');
            }
        }elseif($type==2){
            $currentDate = date('Y-m');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m', strtotime("-$i month", strtotime($currentDate)));
                $dates[] = $date;
            }

            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one).'月';
                $result[$k]['num']=Db::name('smoke_cost_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereMonth('buy_date',$v)
                    ->sum('price');
            }
        }elseif($type==3){
            $currentDate = date('Y');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y', strtotime("-$i year", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $result[$k]['date']=$v;
                $result[$k]['num']=Db::name('smoke_cost_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereYear('buy_date',$v)
                    ->sum('price');
            }
        }
        $data['result']=array_reverse($result);;
        $data['is_vip']=$this->is_vip;
//        $data['y_max'] = max(array_column($result, 'num'));
        return show(1,'success',$data);
    }
    public function level_statistics(){
        $type=input('get.type',1);
        if (!$type){
            $type=1;
        }
        if ($type==1){
            //获取近6日日期
            $currentDate = date('Y-m-d');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m-d', strtotime("-$i days", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one);
                $num=Db::name('smoke_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereDay('smoke_date',$v)
                    ->sum('num');
                $result[$k]['num']=get_level($num);
            }
        }elseif($type==2){
            $currentDate = date('Y-m');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m', strtotime("-$i month", strtotime($currentDate)));
                $dates[] = $date;
            }

            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one).'月';
                $num=Db::name('smoke_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereMonth('smoke_date',$v)
                    ->sum('num');
                if ($k==0){
                    $days=get_diff_day(strtotime(date('Y-m-01')),time());
                }else{
                    $year = $one[0];
                    $month = $one[1];
                    $date = new \DateTime("{$year}-{$month}-01");
                    $days = $date->format('t');
                }
                $num=round($num/$days,2);
                $result[$k]['num']=get_level($num);
            }
        }elseif($type==3){
            $currentDate = date('Y');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y', strtotime("-$i year", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $result[$k]['date']=$v;
                $num=Db::name('smoke_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereYear('smoke_date',$v)
                    ->sum('num');
                if ($k==0){
                    $days=get_diff_day(strtotime(date('Y'). '-01-01 00:00:00'),time());
                }else{
                    $days=getDaysInYear($v);
                }
                $num=round($num/$days,2);
                $result[$k]['num']=get_level($num);
            }
        }
        $data['result']=array_reverse($result);;
        $data['is_vip']=$this->is_vip;
        return show(1,'success',$data);
    }
    public function smoke_log(){
        $type = input('get.type');
        $page = input('param.page', 1, 'intval');
        $limit = input('param.limit', 50, 'intval');
        $where=[];
        $where[] = [
            'user_id', '=',  $this->userId
        ];
        $book = SmokeRecord::where($where)
            ->page($page, $limit)
            ->order('smoke_date desc');
        $count = SmokeRecord::where($where);
        if ($type){
            $num=$book->whereYear('smoke_date',$type)->sum('num');
            $book=$book->whereYear('smoke_date',$type)
                ->column('id,smoke_date,num,smoke_arr');
            $count=$count->whereYear('smoke_date',$type)
                ->count();
            $month=12;
            $days=getDaysInYear($type);
        }else{
            $num=$book->sum('num');
            $book = $book->column('id,smoke_date,num,smoke_arr');
            $count = $count->count();
            //找出多少天和多少月
            //第一天和今天差了多少天
            $first_smoke=Db::name('smoke_record')
                ->where('user_id',$this->userId)
                ->order('smoke_date asc')
                ->limit(1)
                ->value('smoke_date');
            if (!$first_smoke){
                $days=1;
                $month=1;
            }else{
                $days=get_diff_day($first_smoke,time());
                $month=ceil($days/30);
            }
        }
        foreach ($book as $k => $v) {
            $book[$k]['smoke_date']=date("Y/m/d H:i",$v['smoke_date']);
            $smoke=json_decode($v['smoke_arr'],true);
            foreach ($smoke as $kk=>$vv) {
                $smoke[$kk]['name']=Db::name('smoke')
                    ->where('id',$vv['smoke_id'])
                    ->value('name');
            }
            $book[$k]['smoke_arr']=$smoke;
        }
        //处理头部数据
        $top['sum']=$num;
        $top['day_num']=round($num/$days,2);
        $top['month_num']=round($num/$month,2);
        $count = intval(ceil($count / $limit));
        $data['list'] = $book;
        $data['top'] = $top;
        $data['count'] = $count;
        return $this->show(1, '', $data);
    }
    public function smoke_log_export(){
        $user=Db::name('user')
            ->where('id',$this->userId)
            ->find();
        $email=input('get.email');
        $type=input('get.type');
        if ($type){
            $res=Db::name('smoke_record')
                ->where('user_id',$this->userId)
                ->whereYear('smoke_date',$type)
                ->order('smoke_date desc')
                ->column('smoke_date,num,create_time');
        }else{
            $res=Db::name('smoke_record')
                ->where('user_id',$this->userId)
                ->order('smoke_date desc')
                ->column('smoke_date,num,create_time');
        }
        $spreadsheet = new Spreadsheet();
        // 获取当前活动的工作表（默认第一个工作表）
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $sheet->setCellValue('A1', '日期');
        $sheet->setCellValue('B1', '总棵数');
        $sheet->setCellValue('C1', '记录时间');


        // 模拟一些数据，实际应用中可从数据库等获取
        $data_two=[];
        foreach ($res as $k=>$v) {
            $data_two[$k][]=date("Y-m-d H:i",$v['smoke_date']);
            $data_two[$k][]=$v['num'];
            $data_two[$k][]=date("Y-m-d H:i",$v['create_time']);
        }
        // 填充数据行
        $row = 2;
        foreach ($data_two as $rowData) {
            $col = 'A';
            foreach ($rowData as $value) {
                $sheet->setCellValue($col. $row, $value);
                $col++;
            }
            $row++;
        }

        // 创建一个临时文件用于保存Excel内容，这里以xlsx格式为例
        $fileName = 'export/'.$user['invite_code'].'.xlsx';
        $writer = new Xlsx($spreadsheet);
        $writer->save($fileName);
        //存储完之后,发送邮件
        $subject=$user['nickname']."您的数据导出已完成";
        $res=sendMailFile($email,$user['nickname'],$fileName,$subject);
        if ($res){
            return $this->show(1,'导出成功请查看邮箱');
        }
        return show(0,'导出失败');
    }
    /**
     * 处理品牌
     */
    public function add_brand(){
        $provinces = Db::name('smoke_history')
            ->distinct(true)
            ->column('brand');
        $result=[];
        $time=time();
        foreach ($provinces as $k => $v) {
            $result[$k]['name']=$v;
            $result[$k]['category_id']=4;
            $result[$k]['create_time']=$time;
            $result[$k]['update_time']=$time;
        }
        $res=Db::name('smoke_brand')->insertAll($result);
        dd($res);
    }
    /**
     * 去重
     */
    public function del(){
        $sql="SELECT MIN(id) as id,name,img_url,juan_number,tiao_number,reference_price
FROM bingdi_smoke_mainland
GROUP BY name,img_url,juan_number,tiao_number,reference_price;";
        $model=Db::query($sql);
        foreach ($model as $k=>$v){
            Db::name('smoke_mainland')->where('id','<>',$v['id'])
                ->where(['name'=>$v['name'],'img_url'=>$v['img_url'],'juan_number'=>$v['juan_number'],'tiao_number'=>$v['tiao_number'],'reference_price'=>$v['reference_price']])
                ->delete();
        }
        dd(123);
    }
    /**\
     * 处理香烟数据
     */
    public function tool(){
        //
        $page=input('get.page');
        $limit=input('get.limit');
        $res=Db::name('smoke_mainland')
            ->page($page,$limit)
            ->select()
            ->toArray();
        $time=time();
        foreach ($res as $k=>$v){
            sscanf($v['reference_price'], "参考价格： ￥%d /包", $price);
            sscanf($v['actual_price'], "实际价格： ￥%d /盒", $carton_price);
            $result[$k]['category_id']=1;
            $result[$k]['name']=$v['name'];
            $result[$k]['price']=$price;
            $result[$k]['carton_price']=$carton_price;
            $result[$k]['height']=substr($v['length'], 0, -2);
            $result[$k]['filter_height']=substr($v['filter_length'], 0, -2);
            $result[$k]['tar']=substr($v['tar_yield'], 0, -2);
            $result[$k]['nicotine']=substr($v['nicotine_content'], 0, -2);
            $result[$k]['co']=substr($v['co_content'], 0, -2);
            $result[$k]['num']=$v['amount'];
            $result[$k]['pack']=$v['packet_type'];
            $result[$k]['sell']=$v['sale_type'];
            $result[$k]['box_code']=$v['juan_number'];
            $result[$k]['carton_code']=$v['tiao_number'];
            $result[$k]['img']=$v['img_url'];
            $result[$k]['desc']=$v['desc'];
            $result[$k]['brand_id']=$v['brand_id'];

            $result[$k]['create_time']=$time;
            $result[$k]['update_time']=$time;
        }
        dd($result);
        $ress=Db::name('smoke')
            ->insertAll($result);
        dd($ress);
    }
    public function brand(){
        $page=input('get.page')?input('get.page'):1;
        $limit=input('get.limit')?input('get.limit'):1000;
        $res=Db::name('smoke_worldwide')
            ->alias('s')
            ->order('s.id asc')
            ->where('category_id',2)
            ->leftJoin('smoke_brand b','s.brand=b.name')
            ->page($page,$limit)
            ->column('s.id,s.brand,b.id as brand_id');
        foreach ($res as $k=>$v){
            Db::name('smoke_mainland')
                ->where('id',$v['id'])
                ->update(['brand_id'=>$v['brand_id']]);
        }
        dd(123);
    }
}
